USE [BonData]
/****** Object:  Table [dbo].[tblAuthForm]    Script Date: 2/10/2015 4:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAuthForm](
	[AuthFormID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NULL,
	[VendorID] [int] NULL,
	[CardID] [int] NULL,
	[LineNo] [int] NULL,
	[Attention] [nvarchar](50) NULL,
	[PhoneNumber] [nvarchar](30) NULL,
	[FaxNumber] [nvarchar](30) NULL,
	[PaymentFor] [nvarchar](500) NULL,
	[PaymentDate] [datetime2](0) NULL,
	[AuthAmt] [money] NULL,
	[ChargedbyVendor] [money] NULL,
	[Difference] [int] NULL,
	[Preparedby] [nvarchar](50) NULL,
	[Charged] [bit] NULL,
	[IsComm] [bit] NULL,
	[CommAmtRec] [money] NULL,
	[CommRecDate] [datetime2](0) NULL,
	[CommAmt] [money] NULL,
	[ExpCommAmt] [money] NULL,
	[Curr] [varchar](10) NULL,
	ArrivalDate [datetime2](0) NULL,
	Category [varchar](10) NULL,
	[Time] [datetime2](0) NULL,
 CONSTRAINT [PK_tblAuthForm] PRIMARY KEY CLUSTERED 
(
	[AuthFormID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT INTO [dbo].[tblAuthForm] ([GroupID], [VendorID], [CardID], [LineNo],
 [Attention], [PhoneNumber], [FaxNumber], [PaymentFor], [PaymentDate],
 [AuthAmt], [ChargedbyVendor], [Difference], [Preparedby], [Charged],
 [IsComm], [CommAmtRec], [CommRecDate], [CommAmt], [ExpCommAmt])
 select 	 
	CASE 
         WHEN (select GroupID from tblGroup grp where grp.GroupID = amex.GroupID) is not null
		 THEN amex.GroupID
		 WHEN (select GroupID from tblGroup grp where grp.GroupID = amex.[Group Code]) is not null
		 THEN amex.[Group Code]
		 WHEN (select GroupID from tblGroup grp where grp.GroupID = amex.[Group Name]) is not null
		 THEN amex.[Group Name]
		 ELSE NULL
      END  as GroupID,  
	  CASE 
         WHEN (select VendorID from tblVendor vnd where vnd.VendorID = amex.VendorID) is not null
		 THEN amex.VendorID
		 WHEN (select VendorID from tblVendor vnd where vnd.VendorID = amex.[Vendor Name]) is not null
		 THEN amex.[Vendor Name]
		 ELSE NULL
      END  as VendorID, 
	  amex.[BankID],
	  amex.[LineNo],
	  amex.[Attention],
	  amex.[Tel],
	  amex.[Fax], 
	  amex.[Payment For],
	  amex.[Date], 
	  amex.[Authorized Amount], 
	  amex.[$ Charged by Vendor], 
	  amex.[Difference],
	  amex.[Prepared by], 
	  amex.[Charged], 
	  amex.[Is it Commissionable?],
	  amex.[Commission Amount Received], 
	  amex.[Commission Received Date],
	  amex.[Commissionable Amount], 
	  amex.[Expected Commission Amount]
 from [Amex-Vendor] as amex;
GO
DROP TABLE [Amex-Vendor]
GO
ALTER TABLE tblAuthForm
ALTER COLUMN PaymentFor nvarchar(500)
